﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using SW.Lite.Infrastructure.Common;
using SW.Lite.Infrastructure.Helpers;
using SW.Lite.Model;
using IModel = SW.Lite.IDAL.IModel;

namespace SW.Lite.DAL.MsSql
{
    public class Model : IModel
    {
        public bool Exists(int id)
        {
            const string sql = "SELECT COUNT(1) FROM SW_Model WHERE Id=@Id";
            var paramter = new SqlParameter("@Id", SqlDbType.Int) { Value = id };
            return MsSqlHelper.Exists(sql, paramter);
        }

        public bool Exists(string code)
        {
            const string sql = "SELECT COUNT(1) FROM SW_Model WHERE Code=@Code";
            var paramter = new SqlParameter("@Code", SqlDbType.NVarChar) { Value = code };
            return MsSqlHelper.Exists(sql, paramter);
        }

        public ModelModel Single(int id)
        {
            const string sql = "SELECT Id, Code, Title, Description FROM SW_Model WHERE Id=@Id";

            var paramter = new SqlParameter("@Id", SqlDbType.Int) { Value = id };
            return FillSingle(sql, paramter);
        }

        public PageList<ModelModel> All()
        {
            const string sql = "SELECT Id, Code, Title, Description FROM SW_Model";

            return FillList(sql);
        }

        public PageList<ModelModel> All(int pageIndex, int pageSize)
        {
            var start = (pageIndex - 1) * pageSize;
            var end = pageIndex * pageSize;
            var sql =
                string.Format(@"SELECT Id, Code, Title, Description FROM (SELECT ROW_NUMBER() OVER (ORDER BY Id) ROWNO,* FROM SW_Model) A WHERE A.ROWNO BETWEEN {0} AND {1};
            SELECT COUNT(1) FROM SW_Model;", start, end);
            return FillList(sql);
        }

        public ExecuteResult Insert(ModelModel model)
        {
            const string sql = "INSERT INTO SW_Model(Code, Title, Description) VALUES (@Code, @Title, @Description)";

            var parameters = new[]
            {
                new SqlParameter("@Code", SqlDbType.NVarChar) {Value = model.Code},
                new SqlParameter("@Title", SqlDbType.NVarChar) {Value = model.Title},
                new SqlParameter("@Description", SqlDbType.NVarChar) {Value = model.Description}
            };

            var result = MsSqlHelper.Execute(sql, parameters);
            return 0 == result ? new ExecuteResult(false) : new ExecuteResult(true);
        }

        public ExecuteResult Update(ModelModel model)
        {
            const string sql = "UPDATE SW_Model SET Code=@Code, Title=@Title, Description=@Description WHERE Id=@Id ";
            var parameters = new[]
            {
                new SqlParameter("@Id", SqlDbType.Int) {Value = model.Id},
                new SqlParameter("@Code", SqlDbType.NVarChar) {Value = model.Code},
                new SqlParameter("@Title", SqlDbType.NVarChar) {Value = model.Title},
                new SqlParameter("@Description", SqlDbType.NVarChar) {Value = model.Description}
            };

            var result = MsSqlHelper.Execute(sql, parameters);
            return 0 == result ? new ExecuteResult(false) : new ExecuteResult(true);
        }

        public ExecuteResult Delete(int id)
        {
            const string sql = "DELETE FROM SW_Model WHERE Id=@Id";
            var paramter = new SqlParameter("@Id", SqlDbType.Int) { Value = id };
            var result = MsSqlHelper.Execute(sql, paramter);
            return 0 == result ? new ExecuteResult(false) : new ExecuteResult(true);
        }

        public ExecuteResult CreateTable(ModelModel model)
        {
            const string sql = @"CREATE TABLE [dbo].[SW_CT_{0}](
          [ObjectId] [int] IDENTITY(1,1) NOT NULL,
          {1},
          [CreateUserID] [int] NOT NULL DEFAULT (0),
          [CreateTime] [datetime] NOT NULL DEFAULT (GETDATE()),
          [ModifyUserID] [int] NOT NULL DEFAULT (0),
          [ModifyTime] [datetime] NOT NULL DEFAULT (GETDATE()),
          CONSTRAINT [PK_SW_CT_{0}] PRIMARY KEY CLUSTERED ([ObjectId] ASC)
          ) ON [PRIMARY]";

            var sb = new List<string>();
            foreach (var field in model.Fields)
            {
                string strType;
                var strLen = "";
                switch ((FieldType)field.Type)
                {
                    case FieldType.Int:
                        strType = "INT";
                        break;
                    case FieldType.Float:
                        strType = "DECIMAL";
                        strLen = "(" + field.Length.ToString(CultureInfo.InvariantCulture) + ",2)";
                        break;
                    case FieldType.Boolean:
                        strType = "BIT";
                        break;
                    case FieldType.DateTime:
                        strType = "DATETIME";
                        break;
                    default:
                        strType = "NVARCHAR";
                        strLen = field.Length == 0
                            ? "(MAX)"
                            : "(" + field.Length + ")";
                        break;
                }
                sb.Add(String.Format("[{0}] [{1}]{2} {3}", field.Code, strType, strLen, "NULL"));
            }

            var createSql = String.Format(sql, model.Code, String.Join(",\r\n", sb.ToArray()));

            MsSqlHelper.Execute(createSql);
            return new ExecuteResult(true);
        }

        private ModelModel FillSingle(string sql, params SqlParameter[] paramters)
        {
            ModelModel data = null;
            using (var rdr = MsSqlHelper.ExecuteReader(sql, paramters))
            {
                if (rdr.Read())
                    data = ReadData(rdr);
            }
            return data;
        }

        private PageList<ModelModel> FillList(string sql, params SqlParameter[] paramters)
        {
            var list = new PageList<ModelModel>();
            using (var rdr = MsSqlHelper.ExecuteReader(sql, paramters))
            {
                while (rdr.Read())
                    list.Add(ReadData(rdr));
                if (rdr.NextResult() && rdr.Read())
                    list.AllCount = rdr.GetInt32(0);
                else
                    list.AllCount = list.Count;
            }
            return list;
        }

        private ModelModel ReadData(SqlDataReader dr)
        {
            return new ModelModel
            {
                Id = Convert.ToInt32(dr["Id"]),
                Code = Convert.ToString(dr["Code"]),
                Title = Convert.ToString(dr["Title"]),
                Description = Convert.ToString(dr["Description"])
            };
        }
    }
}